﻿using AntdUI;
using Serilog;
using SocketCore.Common.Message;
using System.Data;
using System.Text;
using WorkTool2.Classes.Data;
using WorkTool2.CustomForms;
using WorkTool2.Entities;
using WorkTool2.Helpers;
using WorkTool2.Properties;

namespace WorkTool2.Controls
{
    public partial class DataBaseQuery : UserControl
    {
        private Form form;

        public DataBaseQuery(Form _form)
        {
            form = _form;
            InitializeComponent();
            columns_table.Columns = [
                new Column("ColumnName", "字段名", ColumnAlign.Center).SetVisible(false),
                new Column("ColumnKey", "主键", ColumnAlign.Center).SetVisible(false),
                new Column("ColumnNameAndKey", "字段名", ColumnAlign.Center).SetFixed().SetMaxWidth("30%").SetDefaultFilter(),
                new Column("ColumnType", "字段类型", ColumnAlign.Center).SetMaxWidth("20%"),
                new Column("IsNullable", "可为空", ColumnAlign.Center).SetMaxWidth("10%"),
                new Column("ColumnComment", "字段描述", ColumnAlign.Center).SetMaxWidth("20%"),
                new Column("CharacterSetName", "编码", ColumnAlign.Center).SetMaxWidth("20%"),
                new Column("Extra", "其他", ColumnAlign.Center).SetMaxWidth("20%")
            ];
            dropdown_filter.SelectedValue = "过滤连接";
            splitter2.InvokeIfRequiredAsync(() =>
            {
                splitter2.Collapse();
            });
            panel2.InvokeIfRequiredAsync(() =>
            {
                panel2.Visible = false;
            });
            Init();
        }

        private void Init()
        {
            db_tree.Items.Clear();
            var list = SqlSugarHelper.SqliteDb.Queryable<DbConnectionEntity>().ToList();
            foreach (var db in list)
            {
                var dbItem = new TreeItem(db.Name).SetID("conn").SetName("0").SetSubTitle($"{db.IPAddress}:{db.Port}").SetTag(db);
                switch (db.DbType)
                {
                    case Entities.DbType.MySQL:
                        dbItem.SetIcon(Resources.MySQL);
                        break;

                    case Entities.DbType.SQLServer:
                        dbItem.SetIcon(Resources.SQLServer);
                        break;

                    default:
                        break;
                }
                db_tree.Items.Add(dbItem);
            }
        }

        private async void db_tree_NodeMouseClick(object sender, TreeSelectEventArgs e)
        {
            try
            {
                e.Item.Select = true;
                switch (e.Item.ID)
                {
                    case "conn":
                        if (e.Button == MouseButtons.Right)
                        {
                            AntdUI.ContextMenuStrip.open(this,
                                async menuItem => await QuerySchemata(e.Item, menuItem), [
                                new ContextMenuStripItem("刷新").SetID("reload").SetIcon("ReloadOutlined"),
                                new ContextMenuStripItem("编辑连接").SetID("edit").SetIcon("EditOutlined"),
                                new ContextMenuStripItem("删除").SetID("delete").SetIcon("DeleteOutlined")]);
                        }
                        else
                        {
                            await QuerySchemata(e.Item);
                        }
                        break;

                    case "database":
                        if (e.Button == MouseButtons.Right)
                        {
                            AntdUI.ContextMenuStrip.open(this,
                                menuItem => QueryTable(e.Item, menuItem), [
                                new ContextMenuStripItem("刷新").SetID("reload").SetIcon("ReloadOutlined"),
                                new ContextMenuStripItem("复制").SetID("copy").SetIcon("CopyOutlined")]);
                        }
                        else
                        {
                            QueryTable(e.Item);
                        }
                        break;

                    case "table":
                        if (e.Button == MouseButtons.Right)
                        {
                            AntdUI.ContextMenuStrip.open(this,
                                async menuItem => await QueryColumns(e.Item, menuItem), [
                                new ContextMenuStripItem("刷新").SetID("reload").SetIcon("ReloadOutlined"),
                                new ContextMenuStripItem("复制查询SQL").SetID("copySelect").SetIcon("CopyOutlined"),
                                new ContextMenuStripItem("查看前20条数据").SetID("20").SetIcon("SearchOutlined"),
                                new ContextMenuStripItem("查看前100条数据").SetID("100").SetIcon("SearchOutlined"),
                                new ContextMenuStripItem("查看全部数据").SetID("all").SetIcon("SearchOutlined")]);
                        }
                        else
                        {
                            await QueryColumns(e.Item);
                        }
                        break;

                    default:
                        break;
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex, ex.Message);
                Modal.open(new Modal.Config(form, "错误", ex.Message, TType.Error)
                {
                    CancelText = null,
                    OkType = TTypeMini.Error,
                    OkText = "知道了"
                });
            }
        }

        private async Task QuerySchemata(TreeItem treeItem, ContextMenuStripItem? menuItem = null)
        {
            await Spin.open(form, "正在处理中，请耐心等候...", async config =>
            {
                var entity = treeItem.Tag as DbConnectionEntity;
                if (menuItem != null && menuItem.ID == "delete")
                {
                    if (await SqlSugarHelper.SqliteDb.Deleteable<DbConnectionEntity>(entity).ExecuteCommandAsync() > 0)
                    {
                        treeItem.Remove();
                        Notification.success(form, "删除成功！", "", TAlignFrom.Top);
                    }
                    else
                    {
                        Notification.error(form, "删除失败！", "", TAlignFrom.Top);
                    }
                    return;
                }
                if (menuItem != null && menuItem.ID == "edit")
                {
                    form.InvokeIfRequired(() =>
                    {
                        using var addDbConn = new AddDbConn()
                        {
                            Owner = form,
                            StartPosition = FormStartPosition.CenterParent,
                            DbType = (int)entity.DbType,
                            ConnName = entity.Name,
                            IPAddress = entity.IPAddress,
                            Port = entity.Port,
                            DefaultDbName = entity.DefaultDbName,
                            User = entity.User,
                            Password = entity.Password,
                            Id = entity.Id
                        };
                        if (addDbConn.ShowDialog() == DialogResult.OK)
                        {
                            Init();
                            Notification.success(form, "编辑数据库连接成功！", "", TAlignFrom.Top);
                        }
                    });
                    return;
                }
                if (menuItem != null || treeItem.Name == "0")
                {
                    var sql = string.Empty;
                    var sqlDbType = SqlSugar.DbType.MySql;
                    switch (entity.DbType)
                    {
                        case Entities.DbType.MySQL:
                            sql = "SELECT SCHEMA_NAME AS DbName FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');";
                            sqlDbType = SqlSugar.DbType.MySql;
                            break;

                        case Entities.DbType.SQLServer:
                            sql = "SELECT NAME AS DbName FROM SYS.DATABASES WHERE NAME NOT IN('master', 'tempdb', 'model', 'msdb');";
                            sqlDbType = SqlSugar.DbType.SqlServer;
                            break;

                        default:
                            break;
                    }
                    new SqlSugarHelper().SqlSugarClient(entity.DbConnection, sqlDbType, sqlClient =>
                    {
                        var dataList = sqlClient.Ado.SqlQuery<string>(sql);
                        dataList.ForEach(x => treeItem.Sub.Add(new TreeItem(x).SetID("database").SetName("0").SetTag($"{entity.DbConnection}database={x}").SetIcon("DatabaseOutlined")));
                        treeItem.Name = "1";
                        treeItem.SetFore(Style.ToColor("#52c41a"));
                        treeItem.Expand = true;
                    });
                }
                else
                {
                    treeItem.Expand = !treeItem.Expand;
                }
            });
        }

        private void QueryTable(TreeItem treeItem, ContextMenuStripItem? menuItem = null)
        {
            Spin.open(form, "正在处理中，请耐心等候...", config =>
           {
               try
               {
                   var entity = treeItem.PARENTITEM.Tag as DbConnectionEntity;
                   if (menuItem != null && menuItem.ID == "copy")
                   {
                       form.InvokeIfRequired(() =>
                       {
                           Clipboard.SetText(treeItem.Text);
                       });
                       Notification.info(form, "已复制！", "", TAlignFrom.Top);
                       return;
                   }
                   if (menuItem != null || treeItem.Name == "0")
                   {
                       var sql = string.Empty;
                       var sqlDbType = SqlSugar.DbType.MySql;
                       switch (entity.DbType)
                       {
                           case Entities.DbType.MySQL:
                               sql = $"SELECT TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{treeItem.Text}';";
                               sqlDbType = SqlSugar.DbType.MySql;
                               break;

                           case Entities.DbType.SQLServer:
                               sql = $"SELECT TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '{treeItem.Text}';";
                               sqlDbType = SqlSugar.DbType.SqlServer;
                               break;

                           default:
                               break;
                       }
                       new SqlSugarHelper().SqlSugarClient($"{entity.DbConnection} database={treeItem.Text};", sqlDbType, sqlClient =>
                       {
                           var dataList = sqlClient.Ado.SqlQuery<string>(sql);
                           dataList.ForEach(x => treeItem.Sub.Add(new TreeItem(x).SetID("table").SetName("0").SetTag(treeItem.Tag).SetFore(Style.ToColor("#08979c")).SetIcon("TableOutlined")));
                           treeItem.Name = "1";
                           treeItem.SetFore(Style.ToColor("#52c41a"));
                           treeItem.Expand = true;
                       });
                   }
                   else
                   {
                       treeItem.Expand = !treeItem.Expand;
                   }
               }
               catch (Exception ex)
               {
                   Log.Error(ex, ex.Message);
                   Modal.open(new Modal.Config(form, "错误", ex.Message, TType.Error)
                   {
                       CancelText = null,
                       OkType = TTypeMini.Error,
                       OkText = "知道了"
                   });
               }
           });
        }

        private async Task QueryColumns(TreeItem treeItem, ContextMenuStripItem? menuItem = null)
        {
            await Spin.open(form, "正在处理中，请耐心等候...", async config =>
            {
                try
                {
                    if (menuItem != null && menuItem.ID != "reload")
                    {
                        if (columns_table.Tag?.ToString() != treeItem.Text)
                            await QueryColumns(treeItem);
                        if (menuItem.ID == "copySelect")
                        {
                            form.InvokeIfRequired(() =>
                            {
                                var data = columns_table.DataSource as List<DbTableColumns>;
                                if (data.Count != 0)
                                {
                                    var sql = new StringBuilder("SELECT ");
                                    sql.Append(string.Join(",", data.Select(x => x.ColumnName)));
                                    sql.Append(" FROM ");
                                    sql.Append(treeItem.Text);
                                    Clipboard.SetText(sql.ToString());
                                    Notification.info(form, "已复制！", "", TAlignFrom.Top);
                                }
                            });
                        }
                        else
                        {
                            int? count = menuItem.ID switch
                            {
                                "20" => 20,
                                "100" => 100,
                                _ => null,
                            };
                            splitter2.InvokeIfRequiredAsync(() =>
                            {
                                splitter2.Expand();
                            });
                            panel2.InvokeIfRequiredAsync(() =>
                            {
                                panel2.Visible = true;
                            });
                            QueryData(treeItem, count: count);
                        }
                    }
                    else
                    {
                        data_table.InvokeIfRequiredAsync(() =>
                        {
                            data_table.Columns.Clear();
                            data_table.DataSource = null;
                        });
                        splitter2.InvokeIfRequiredAsync(() =>
                        {
                            splitter2.Collapse();
                        });
                        panel2.InvokeIfRequiredAsync(() =>
                        {
                            panel2.Visible = false;
                        });
                        var entity = treeItem.PARENTITEM.PARENTITEM.Tag as DbConnectionEntity;
                        var sql = string.Empty;
                        var sqlDbType = SqlSugar.DbType.MySql;
                        switch (entity.DbType)
                        {
                            case Entities.DbType.MySQL:
                                sql = $"SELECT COLUMN_NAME AS 'ColumnName', COLUMN_KEY AS 'ColumnKey', COLUMN_TYPE AS 'ColumnType', IS_NULLABLE AS 'IsNullable', COLUMN_COMMENT AS 'ColumnComment', CHARACTER_SET_NAME AS 'CharacterSetName', EXTRA AS 'Extra' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{treeItem.PARENTITEM.Text}' AND TABLE_NAME = '{treeItem.Text}';";
                                sqlDbType = SqlSugar.DbType.MySql;
                                break;

                            case Entities.DbType.SQLServer:
                                sql = @$"SELECT c.COLUMN_NAME AS 'ColumnName',
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
            WHERE kcu.TABLE_NAME = c.TABLE_NAME
            AND kcu.COLUMN_NAME = c.COLUMN_NAME
            AND kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
        ) THEN 'PRI'
        ELSE ''
    END AS 'ColumnKey',
    DATA_TYPE +
        CASE
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' +
                CASE
                    WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'
                    ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10))
                END + ')'
            WHEN NUMERIC_PRECISION IS NOT NULL AND NUMERIC_SCALE IS NOT NULL THEN '(' +
                CAST(NUMERIC_PRECISION AS VARCHAR(10)) + ',' +
                CAST(NUMERIC_SCALE AS VARCHAR(10)) + ')'
            WHEN NUMERIC_PRECISION IS NOT NULL THEN '(' +
                CAST(NUMERIC_PRECISION AS VARCHAR(10)) + ')'
            ELSE ''
        END AS 'ColumnType',
    c.IS_NULLABLE AS 'IsNullable',
    ISNULL(ep.value, '') AS 'ColumnComment', -- 获取扩展属性中的注释
    c.CHARACTER_SET_NAME AS 'CharacterSetName',
    CASE
        WHEN COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'IsIdentity') = 1 THEN 'auto_increment'
        ELSE ''
    END AS 'Extra'
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN sys.extended_properties ep
    ON ep.major_id = OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME)
    AND ep.minor_id = COLUMNPROPERTY(OBJECT_ID(c.TABLE_SCHEMA + '.' + c.TABLE_NAME), c.COLUMN_NAME, 'ColumnId')
    AND ep.name = 'MS_Description' -- 标准注释属性名
WHERE c.TABLE_CATALOG = '{treeItem.PARENTITEM.Text}'
AND c.TABLE_NAME = '{treeItem.Text}';";
                                sqlDbType = SqlSugar.DbType.SqlServer;
                                break;

                            default:
                                break;
                        }
                        new SqlSugarHelper().SqlSugarClient($"{entity.DbConnection} database={treeItem.PARENTITEM.Text};", sqlDbType, sqlClient =>
                        {
                            var dataList = sqlClient.Ado.SqlQuery<DbTableColumns>(sql);
                            columns_table.DataSource = dataList;
                            columns_table.Tag = treeItem.Text;
                            treeItem.Name = "1";
                            treeItem.Expand = true;
                        });
                    }
                }
                catch (Exception ex)
                {
                    Log.Error(ex, ex.Message);
                }
            });
        }

        private void QueryData(TreeItem treeItem, string? columns = null, string? where = null, int? count = null)
        {
            Spin.open(form, "正在处理中，请耐心等候...", config =>
            {
                var entity = treeItem.PARENTITEM.PARENTITEM.Tag as DbConnectionEntity;
                var sql = new StringBuilder("SELECT ");
                var sqlDbType = SqlSugar.DbType.MySql;
                var mysqlLimit = string.Empty;
                var tableName = string.Empty;
                switch (entity.DbType)
                {
                    case Entities.DbType.MySQL:
                        if (count.HasValue)
                            mysqlLimit = $"LIMIT {count.Value} ";
                        tableName = $"{treeItem.PARENTITEM.Text}.{treeItem.Text} ";
                        sqlDbType = SqlSugar.DbType.MySql;
                        break;

                    case Entities.DbType.SQLServer:
                        if (count.HasValue)
                            sql.Append($"TOP {count.Value} ");
                        tableName = $"{treeItem.PARENTITEM.Text}.dbo.{treeItem.Text} ";
                        sqlDbType = SqlSugar.DbType.SqlServer;
                        break;

                    default:
                        break;
                }
                if (!string.IsNullOrEmpty(columns))
                {
                    sql.Append($"{columns} ");
                }
                else
                {
                    sql.Append("* ");
                }
                sql.Append($"FROM {tableName} ");
                if (!string.IsNullOrEmpty(where))
                {
                    sql.Append($"{label_where.Text} {where} ");
                }
                sql.Append(mysqlLimit);
                new SqlSugarHelper().SqlSugarClient($"{entity.DbConnection} database={treeItem.PARENTITEM.Text};", sqlDbType, sqlClient =>
                {
                    data_table.Columns.Clear();
                    var dataTable = sqlClient.Ado.GetDataTable(sql.ToString());
                    foreach (DataColumn item in dataTable.Columns)
                    {
                        data_table.Columns.Add(new Column(item.ColumnName, item.ColumnName, ColumnAlign.Center).SetMaxWidth("20%").SetEllipsis());
                    }
                    data_table.DataSource = dataTable;
                });
            });
        }

        private Table.CellStyleInfo? table_SetRowStyle(object sender, TableSetRowStyleEventArgs e)
        {
            if (e.Index % 2 == 0)
            {
                return new Table.CellStyleInfo
                {
                    BackColor = Config.IsDark ? Color.Gray : Color.WhiteSmoke,
                };
            }
            return null;
        }

        private void splitter2_SplitPanelStateChanged(object sender, BoolEventArgs e)
        {
            panel2.InvokeIfRequiredAsync(() =>
            {
                panel2.Visible = !panel2.Visible;
            });
        }

        private void btn_select_Click(object sender, EventArgs e)
        {
            Spin.open(form, "正在处理中，请耐心等候...", config =>
            {
                var treeItem = db_tree.SelectItem;
                var selectColumns = new List<string>();
                foreach (var index in columns_table.SelectedIndexs)
                {
                    selectColumns.Add((columns_table.DataSource as List<DbTableColumns>)[index - 1].ColumnName);
                }
                QueryData(treeItem, string.Join(",", selectColumns), input_where.Text, 1000);
            });
        }

        private void dropdown_filter_SelectedValueChanged(object sender, ObjectNEventArgs e)
        {
            dropdown_filter.Text = e.Value.ToString();
            input_filter.InvokeIfRequired(() =>
            {
                input_filter.PlaceholderText = "按名称" + e.Value.ToString();
            });
            if (!string.IsNullOrEmpty(input_filter.Text))
                FilterTree();
        }

        private void input_filter_TextChanged(object sender, EventArgs e)
        {
            FilterTree();
        }

        private void FilterTree()
        {
            if (!string.IsNullOrEmpty(input_filter.Text))
            {
                var filter = input_filter.Text.Trim().ToLower();
                switch (dropdown_filter.SelectedValue)
                {
                    case "过滤连接":
                        foreach (var conn in db_tree.Items)
                        {
                            var dbConnection = (conn.Tag as DbConnectionEntity);
                            if (dbConnection.Name.ToLower().Contains(filter) || $"{dbConnection.IPAddress}:{dbConnection.Port}".ToLower().Contains(filter))
                            {
                                conn.Visible = true;
                            }
                            else
                            {
                                conn.Visible = false;
                            }
                        }
                        break;

                    case "过滤数据库":
                        foreach (var conn in db_tree.Items)
                        {
                            var connVisible = false;
                            foreach (var db in conn.Sub)
                            {
                                if (db.Text.ToLower().Contains(filter))
                                {
                                    connVisible = true;
                                    db.Visible = true;
                                }
                                else
                                {
                                    db.Visible = false;
                                }
                            }
                            conn.Visible = connVisible;
                        }
                        break;

                    case "过滤表":
                        foreach (var conn in db_tree.Items)
                        {
                            var connVisible = false;
                            foreach (var db in conn.Sub)
                            {
                                var dbVisible = false;
                                foreach (var table in db.Sub)
                                {
                                    if (table.Text.ToLower().Contains(filter))
                                    {
                                        connVisible = true;
                                        dbVisible = true;
                                        table.Visible = true;
                                    }
                                    else
                                    {
                                        table.Visible = false;
                                    }
                                }
                                db.Visible = dbVisible;
                            }
                            conn.Visible = connVisible;
                        }
                        break;

                    default:
                        break;
                }
            }
            else
            {
                foreach (var conn in db_tree.Items)
                {
                    foreach (var db in conn.Sub)
                    {
                        foreach (var table in db.Sub)
                        {
                            table.Visible = true;
                        }
                        db.Visible = true;
                    }
                    conn.Visible = true;
                }
            }
        }

        private void btn_addConn_Click(object sender, EventArgs e)
        {
            using var addDbConn = new AddDbConn()
            {
                Owner = form,
                StartPosition = FormStartPosition.CenterParent
            };
            if (addDbConn.ShowDialog() == DialogResult.OK)
            {
                Init();
                Notification.success(form, "新增数据库连接成功！", "", TAlignFrom.Top);
            }
        }

        private void columns_table_CellDoubleClick(object sender, TableClickEventArgs e)
        {
            if (e.Record is DbTableColumns data)
            {
                var columnsData = JsonHelper.Serialize(data).Deserialize<Dictionary<string, string>>();
                var text = e.Column.Key == "ColumnNameAndKey" ? columnsData["ColumnName"] : columnsData[e.Column.Key];
                if (string.IsNullOrEmpty(text))
                    return;
                Clipboard.SetText(text);
                Notification.info(form, "已复制！", "", TAlignFrom.Top);
            }
        }

        private void data_table_CellClick(object sender, TableClickEventArgs e)
        {
            if (e.Record is DataRow row)
            {
                Popover.open(new Popover.Config(data_table, row[e.ColumnIndex]) { Offset = e.Rect });
            }
        }

        private void data_table_CellDoubleClick(object sender, TableClickEventArgs e)
        {
            if (e.Record is DataRow row)
            {
                var text = row[e.ColumnIndex]?.ToString();
                if (string.IsNullOrEmpty(text))
                    return;
                Clipboard.SetText(text);
                Notification.info(form, "已复制！", "", TAlignFrom.Top);
            }
        }
    }
}